package com.teaching.management.repository;

import com.teaching.management.entity.CourseSelection;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.util.List;

@Repository
public interface CourseSelectionRepository extends JpaRepository<CourseSelection, Integer> {
    List<CourseSelection> findByStudentId(String studentId);

    List<CourseSelection> findByStudentIdAndSemester(String studentId, String semester);

    List<CourseSelection> findByStudentIdAndStatus(String studentId, String status);

    List<CourseSelection> findByCourseId(String courseId);

    int countByStudentIdAndStatus(String studentId, String status);

    int countByCourseId(String courseId);

    @Transactional
    @Modifying
    @Query("DELETE FROM CourseSelection cs WHERE cs.courseId = :courseId")
    void deleteByCourseId(@Param("courseId") String courseId);

    @Query("SELECT COUNT(DISTINCT cs.studentId) FROM CourseSelection cs WHERE cs.courseId IN (SELECT c.courseId FROM Course c WHERE c.teacherId = :teacherId)")
    int countStudentsByTeacherId(@Param("teacherId") String teacherId);

    @Query("SELECT AVG(cs.score) FROM CourseSelection cs WHERE cs.studentId = :studentId AND cs.status = '已修'")
    BigDecimal getAvgScoreByStudentId(@Param("studentId") String studentId);

    @Query("SELECT SUM(c.credit) FROM Course c JOIN CourseSelection cs ON c.courseId = cs.courseId WHERE cs.studentId = :studentId AND cs.status = '已修'")
    Integer getTotalCreditsByStudentId(@Param("studentId") String studentId);
}